package com.oristand.printer.address.util;

import com.oristand.printer.address.model.LabelDetail;
import com.oristand.printer.address.model.PrintRecord;
import javafx.application.Platform;
import org.apache.commons.codec.digest.DigestUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author chenkailiang
 * @date 2020/9/3
 */
public class DbUtil {

    public static  SqliteUtil printRecordDbUtil;
    public static  SqliteUtil printLabelRecordDbUtil;
    public static  SqliteUtil printerConfigurationDbUtil;

    static {
        try {
            printRecordDbUtil = new SqliteUtil(Constants.LOCAL_PRINT_RECORD);
            printLabelRecordDbUtil = new SqliteUtil(Constants.LOCAL_PRINT_LABEL_RECORD);
            printerConfigurationDbUtil = new SqliteUtil(Constants.LOCAL_PRINTER_CONFIGURATION);
        } catch (ClassNotFoundException | SQLException e ) {
            Platform.runLater(()-> CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }
    }


    /**
     * 更新db文件
     */
    public static void updateDb(){
        checkAndUpdateDbFile(Constants.LOCAL_PRINT_RECORD);
        checkAndUpdateDbFile(Constants.LOCAL_PRINT_LABEL_RECORD);
        checkAndUpdateDbFile(Constants.LOCAL_PRINTER_CONFIGURATION);
    }

    /**
     * 检查db文件是否有效
     *
     * @param filePath 文件路径
     */
    private static void checkAndUpdateDbFile(String filePath){
        File file  = new File(filePath);

        try {
            //如果db文件不存在则创建文件
            if(!file.exists() || file.isDirectory()){
                boolean createResult = file.createNewFile();
                if(createResult){
                    CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,null);
                }
            }
            SqliteUtil sqliteUtil = new SqliteUtil(filePath);
            if (Constants.LOCAL_PRINT_RECORD.equals(filePath)) {

                //查看表是否存在，不存在则创建表
                String sql = getTableSql(Constants.PRINT_RECORD_NAME );
                int count = sqliteUtil.executeQuery(sql);
                if (count == 0) {
                    printRecordDbUtil.executeUpdate("create table printRecord(printDate varchar(20),fileName varchar(100),md5 varchar(100),printResult varchar(20),printLabelCount int);");
                }else{
                    //如果文件存在，查看表的数据结构
                    int columnCount = getTableColumns(printRecordDbUtil);
                    int columns = 5;
                    if(columnCount != columns){
                        printRecordDbUtil.executeUpdate("drop table printRecord;");
                        printRecordDbUtil.executeUpdate("create table printRecord(printDate varchar(20),fileName varchar(100),md5 varchar(100),printResult varchar(20),printLabelCount int);");
                    }
                }
            } else if (Constants.LOCAL_PRINT_LABEL_RECORD.equals(filePath)) {
                String sql = getTableSql(Constants.PRINT_LABEL_RECORD_NAME );
                int count = printLabelRecordDbUtil.executeQuery(sql);
                if (count == 0) {
                    printLabelRecordDbUtil.executeUpdate("create table printLabelRecord(orderId varchar(50),itemCode varchar(30),trackingNumber varchar(50),fileName varchar(100),printDate varchar(20));");
                }
            }else {
                String sql =getTableSql( Constants.PRINTER_CONFIGURATION_NAME );
                int count = printerConfigurationDbUtil.executeQuery(sql);
                if (count == 0) {
                    printerConfigurationDbUtil.executeUpdate("create table printerConfiguration(connectionType varchar(30),printerName varchar(100),ipAddress varchar(50),port varchar(20));");
                }
            }
        } catch (Exception e) {
            Platform.runLater(()-> CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }

    }

    /**
     * 获取查询表格的sql
     * @param tableName 表名
     * @return string
     */
    private static String getTableSql(String tableName){
        return "select count(*) from sqlite_master where type = 'table' and name = '" + tableName +"'";
    }


    /**
     * 获取表的列数
     * @param sqliteUtil sqliteUtil
     * @return 列数
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     */
    private static int getTableColumns(SqliteUtil sqliteUtil) throws SQLException, ClassNotFoundException {
        //如果文件存在，查看表的数据结构
        String findColumn = " PRAGMA table_info(["+Constants.PRINT_RECORD_NAME+"]);";
        return sqliteUtil.executeQuery(findColumn,(ResultSet rs) ->{
            int index = 0;
            try {
                while (rs.next()){
                    index++;
                }
            } catch (SQLException e) {
                Platform.runLater(()-> CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
            }
            return index;
        });
    }

    /**
     * 根据打印记录查找对应的label
     *
     * @param printRecord 打印记录
     * @return label列表
     */
    public static List<LabelDetail> getLabelByPrintRecord(PrintRecord printRecord){
        try {
            //查找打印记录
            SqliteUtil sqliteUtil = new SqliteUtil(Constants.LOCAL_PRINT_LABEL_RECORD);
            String sql = "select orderId,itemCode,trackingNumber,printDate from printLabelRecord where fileName = '"+printRecord.getFileName()+"' and printDate = '"+printRecord.getPrintDate()+"'";
            return  sqliteUtil.executeQuery(sql, CommonMethodUtil :: getLabelFromResultSet);
        } catch (Exception  e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }
        return null;
    }

    /**
     * 保存文件打印记录
     *
     * @param time 打印时间
     */
    public static void savePrintRecord(String time,String md5,String printResult,int printLabelCount,String fileName){
        try {
            SqliteUtil sqliteUtil = new SqliteUtil(Constants.LOCAL_PRINT_RECORD);
            String sql = "insert into printRecord values('"+time+"','"+fileName+"','"+md5+"','"+printResult+"',"+printLabelCount+");";
            sqliteUtil.executeUpdate(sql);
        } catch (Exception e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }
    }

    /**
     * 查看文件是否被打印过
     * @param file 文件
     * @return  true/false
     */
    public static boolean checkFilePrinted(File file){
        try {
            String md5 = DigestUtils.md5Hex(new FileInputStream(file));
            String sql = "select count(*) from printRecord where  md5 = '"+md5+"'";
            return DbUtil.printRecordDbUtil.executeQuery(sql) > 0;
        } catch (ClassNotFoundException |SQLException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }  catch (IOException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,e));
        }
        return false;
    }

}
